"""empty message

Revision ID: 194a5a2a44ef
Revises: caf96244e10b
Create Date: 2018-05-21 01:54:54.296053

"""

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '194a5a2a44ef'
down_revision = 'caf96244e10b'

class ReplaceableObject(object):
    def __init__(self, name, sqltext):
        self.name = name
        self.sqltext = sqltext

update_rating_func = ReplaceableObject(
    "update_rating(rating text)",
    """
    RETURNS text AS $$
    DECLARE
        r NUMERIC;
    BEGIN
        r = cast(rating as NUMERIC);
        IF r < 0 OR r > 5 THEN
            RETURN '0';
        END IF;
        r = round(r*2) / 2;
        RETURN cast(r as VARCHAR);
    EXCEPTION WHEN invalid_text_representation THEN
        RETURN '0';
    END;
    $$
    STRICT
    LANGUAGE plpgsql IMMUTABLE;
    """)


def upgrade():
    op.create_or_replace_sp(update_rating_func)
    op.execute("UPDATE feedback SET rating=update_rating(rating)")
    op.execute("DROP FUNCTION update_rating(text)")

    op.alter_column('feedback', 'rating',
               existing_type=sa.VARCHAR(),
               type_=sa.Float(),
               existing_nullable=False,
               postgresql_using='rating::double precision')


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('feedback', 'rating',
               existing_type=sa.Float(),
               type_=sa.VARCHAR(),
               existing_nullable=False)
    # ### end Alembic commands ###
